﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class EdiLogDA : IEdiLogDA
    {
        private string IS_SUCCESS = "@IS_SUCCESS";
        private string LOG_DATE = "@LOG_DATE";
        private string LOG_DESCR = "@LOG_DESCR";
        private string SQL_CHECK_EDI_LOG_ID_UNIQUE = " SELECT COUNT(1) FROM sysadm.EDI_LOG WHERE ROWID = @ROWID ";
        private string SQL_DELETE_EDI_LOG = " DELETE FROM sysadm.EDI_LOG WHERE ROWID = @ROWID ";
        private string SQL_INSERT_EDI_LOG = " INSERT INTO sysadm.EDI_LOG ( WH_ID, USER_ID, LOG_DATE, LOG_DESCR, IS_SUCCESS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10) VALUES ( @WH_ID, @USER_ID, GETDATE(), @LOG_DESCR, @IS_SUCCESS, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10)  ";
        private string SQL_SELECT_ALL_EDI_LOG = " SELECT  ROWID, WH_ID, USER_ID, LOG_DATE, LOG_DESCR, IS_SUCCESS, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10  FROM  sysadm.EDI_LOG ";
        private string SQL_UPDATE_EDI_LOG = " UPDATE sysadm.EDI_LOG SET WH_ID = @WH_ID, USER_ID = @USER_ID, LOG_DATE = GETDATE(), LOG_DESCR = @LOG_DESCR, IS_SUCCESS = @IS_SUCCESS, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10 WHERE ROWID = @ROWID  ";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string USER_ID = "@USER_ID";
        private string WH_ID = "@WH_ID";

        public bool CheckEdiLogIDUnique(string ediLogID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", ediLogID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_EDI_LOG_ID_UNIQUE, paras).ToString() == "1");
        }

        public bool CheckEdiLogIDUnique(DataBase dataBase, DbTransaction tran, string ediLogID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", ediLogID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_EDI_LOG_ID_UNIQUE, paras).ToString() == "1");
        }

        public int DeleteEdiLog(List<string> ediLogID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < ediLogID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", ediLogID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_EDI_LOG, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteEdiLog(DataBase dataBase, DbTransaction tran, List<string> ediLogID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < ediLogID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", ediLogID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_EDI_LOG, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllEdiLog()
        {
            string sql = this.SQL_SELECT_ALL_EDI_LOG;
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllEdiLog(DataBase dataBase, DbTransaction tran)
        {
            string sql = this.SQL_SELECT_ALL_EDI_LOG;
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public EdiLogInfo GetEdiLogByID(string edilogID)
        {
            string sql = this.SQL_SELECT_ALL_EDI_LOG + " WHERE ROWID = @ROWID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", edilogID) };
            EdiLogInfo ediLogInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    ediLogInfo = new EdiLogInfo((reader["ROWID"].ToString() != "") ? decimal.Parse(reader["ROWID"].ToString()) : 0M, reader["WH_ID"].ToString(), reader["USER_ID"].ToString(), (reader["LOG_DATE"].ToString() != "") ? DateTime.Parse(reader["LOG_DATE"].ToString()) : new DateTime(), reader["LOG_DESCR"].ToString(), reader["IS_SUCCESS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString());
                }
            }
            return ediLogInfo;
        }

        public EdiLogInfo GetEdiLogByID(DataBase dataBase, DbTransaction tran, string edilogID)
        {
            string sql = this.SQL_SELECT_ALL_EDI_LOG + " WHERE ROWID = @ROWID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", edilogID) };
            EdiLogInfo ediLogInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                ediLogInfo = new EdiLogInfo((reader["ROWID"].ToString() != "") ? decimal.Parse(reader["ROWID"].ToString()) : 0M, reader["WH_ID"].ToString(), reader["USER_ID"].ToString(), (reader["LOG_DATE"].ToString() != "") ? DateTime.Parse(reader["LOG_DATE"].ToString()) : new DateTime(), reader["LOG_DESCR"].ToString(), reader["IS_SUCCESS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return ediLogInfo;
        }

        public DataSet GetEdiLogByQueryList(List<string> sqlWhere, EdiLogQueryEntity edilogQuery)
        {
            string temp = this.SQL_SELECT_ALL_EDI_LOG;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!edilogQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, edilogQuery.CurrentPage, edilogQuery.PageSize, edilogQuery.SortField, edilogQuery.SortDirection);
            }
            else if (!string.IsNullOrEmpty(edilogQuery.SortField))
            {
                temp = temp + " ORDER BY " + edilogQuery.SortField;
                if (!string.IsNullOrEmpty(edilogQuery.SortDirection))
                {
                    temp = temp + " " + edilogQuery.SortDirection;
                }
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetEdiLogByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, EdiLogQueryEntity edilogQuery)
        {
            string temp = this.SQL_SELECT_ALL_EDI_LOG;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!edilogQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, edilogQuery.CurrentPage, edilogQuery.PageSize, edilogQuery.SortField, edilogQuery.SortDirection);
            }
            else if (!string.IsNullOrEmpty(edilogQuery.SortField))
            {
                temp = temp + " ORDER BY " + edilogQuery.SortField;
                if (!string.IsNullOrEmpty(edilogQuery.SortDirection))
                {
                    temp = temp + " " + edilogQuery.SortDirection;
                }
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertEdiLog(EdiLogInfo ediLogInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = this.Set_EdiLog_Parameters(ediLogInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EDI_LOG, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertEdiLog(DataBase dataBase, DbTransaction tran, EdiLogInfo ediLogInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_EdiLog_Parameters(ediLogInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EDI_LOG, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertEdiLogs(List<EdiLogInfo> edilogInfos)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                foreach (EdiLogInfo ediLogInfo in edilogInfos)
                {
                    SqlParameter[] paras = this.Set_EdiLog_Parameters(ediLogInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EDI_LOG, paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertEdiLogs(DataBase dataBase, DbTransaction tran, List<EdiLogInfo> edilogInfos)
        {
            int result = 0;
            try
            {
                foreach (EdiLogInfo ediLogInfo in edilogInfos)
                {
                    SqlParameter[] paras = this.Set_EdiLog_Parameters(ediLogInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EDI_LOG, paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_EdiLog_Parameters(EdiLogInfo ediLogInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.USER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LOG_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.LOG_DESCR, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.IS_SUCCESS, SqlDbType.VarChar, 50), new SqlParameter(this.UDF1, SqlDbType.VarChar, 50), new SqlParameter(this.UDF2, SqlDbType.VarChar, 50), new SqlParameter(this.UDF3, SqlDbType.VarChar, 50), new SqlParameter(this.UDF4, SqlDbType.VarChar, 50), new SqlParameter(this.UDF5, SqlDbType.VarChar, 50), new SqlParameter(this.UDF6, SqlDbType.VarChar, 50), new SqlParameter(this.UDF7, SqlDbType.VarChar, 50), new SqlParameter(this.UDF8, SqlDbType.VarChar, 50), new SqlParameter(this.UDF9, SqlDbType.VarChar, 50), new SqlParameter(this.UDF10, SqlDbType.VarChar, 50) };
            paramArray[0].Value = ediLogInfo.WhID;
            paramArray[1].Value = ediLogInfo.UserID;
            if (ediLogInfo.LogDate != new DateTime())
            {
                paramArray[2].Value = ediLogInfo.LogDate;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.LogDescr))
            {
                paramArray[3].Value = ediLogInfo.LogDescr;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.IsSuccess))
            {
                paramArray[4].Value = ediLogInfo.IsSuccess;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf1))
            {
                paramArray[5].Value = ediLogInfo.Udf1;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf2))
            {
                paramArray[6].Value = ediLogInfo.Udf2;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf3))
            {
                paramArray[7].Value = ediLogInfo.Udf3;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf4))
            {
                paramArray[8].Value = ediLogInfo.Udf4;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf5))
            {
                paramArray[9].Value = ediLogInfo.Udf5;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf6))
            {
                paramArray[10].Value = ediLogInfo.Udf6;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf7))
            {
                paramArray[11].Value = ediLogInfo.Udf7;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf8))
            {
                paramArray[12].Value = ediLogInfo.Udf8;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf9))
            {
                paramArray[13].Value = ediLogInfo.Udf9;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ediLogInfo.Udf10))
            {
                paramArray[14].Value = ediLogInfo.Udf10;
                return paramArray;
            }
            paramArray[14].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateEdiLog(EdiLogInfo ediLogInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = this.Set_EdiLog_Parameters(ediLogInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EDI_LOG, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateEdiLog(DataBase dataBase, DbTransaction tran, EdiLogInfo ediLogInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = this.Set_EdiLog_Parameters(ediLogInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EDI_LOG, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

